package com.pub.database;

/**
 * <p>Title: </p>
 * <p>Description: </p>
 * <p>Copyright: Copyright (c) 2003</p>
 * <p>Company: </p>
 * @author not attributable
 * @version 1.0
 */

import java.io.*;
import java.util.*;
import java.sql.*;
import javax.servlet.http.*;

/**
 * The Databse Connection Bean. One Bean for one connection.
 * If the Bean Creation used by the outside connection, it will have no right to
 * close the db connection. Only it can be closed outside.
 *
 */

public class DBBean implements javax.servlet.http.HttpSessionBindingListener {
  private String  dbType   = null;

  //private boolean extraBit = false; //Judget If the connection accept from outside.

  private Connection        conn    = null;  //The DB Connection For Bean --the only one
  private PreparedStatement preStmt = null;
  private Statement         stmt    = null;
  private ResultSet         rs      = null;

  /*private DBBean() {
     init();
     isSet = true;
  }*/

  /**
   * Accept the outside db connection, so without close connection right
   * @param dbconn. The outside db connection.
   */
  public DBBean(Connection dbconn,String pstrDBType) {
    try {
      if ( dbconn != null && !dbconn.isClosed()) {
        conn = dbconn;
        dbType=pstrDBType;
        //extraBit = true;
      } // else throw db_exception.
    } catch (SQLException se) { System.out.println("DBBean.java (DBBean) --> Set OutSide connection Exception:" + se.toString()); }
    //init();
  }

  /**
   * Create The DB_Bean Object for dbName driver type
   * @param dbName. The Database Driver Type,Such as Oracle,Access
   * @param dbIDL.  The Assign DB Identified String.
   * @param dbusr.  The DB auth user.
   * @param dbpwd.  The password for db auth user.

  private DBBean(String dbName,String dbIDL,String dbusr,String dbpwd) {
     if ( dbName != null && !dbName.equals("") ) {
       try {
         dbDriver = rb.getString("DB_DRIVER");
         dbPrefix = rb.getString("DB_PREFIX");
       }
       catch (Exception e) {
         System.out.println("\n*** DBBean.java (DBBean) --> 提示訊息不需理會 ， DB_Config.properties 請將下列變數值做更換，不換也無所謂啦!!" + "\nORA_DRIVER --> DB_DRIVER" + "\nORA_PREFIX --> DB_PREFIX");
         try {
           dbDriver = rb.getString("ORA_DRIVER");
           dbPrefix = rb.getString("ORA_PREFIX");
         }
         catch (Exception e1) {
           System.out.println("\n*** DBBean.java (DBBean) --> 超級嚴重的錯誤 , 請檢查 DB_Config.properties 下列兩個設定值是否存在! ***" +
                              "\nORA_DRIVER" +
                              "\nORA_PREFIX");
         }
       } //End-try
       dbLocale = dbIDL;
       dbUser   = dbusr;
       dbPwd    = dbpwd;
     }
     init();
  }*/

  /** DB Bean Set OutSide connection
    * @param dbconn. The Connection OutSide.
    */
  public void setDBBean(Connection dbconn) {
    try {
      if ( dbconn != null && !dbconn.isClosed()) {
        conn = dbconn;
        //extraBit = true;
      }
    } catch (SQLException se) { System.out.println("DBBean.java (setDBBean) --> Set OutSide connection Exception:" + se.toString()); }
  }
  /** DB Bean Set OutSide connection
    * @param dbName. The Database Driver Type,Such as Oracle,Access
    * @param dbIDL. The Locale.
    * @param dbusr. The User Name of DB connection.
    * @param dbpwd. The Password of DB connection.
    */
  /*private void setDBBean(String dbName,String dbIDL,String dbusr,String dbpwd) {
     if ( dbName != null && !dbName.equals("") ) {
       try {
         dbDriver = rb.getString("DB_DRIVER");
         dbPrefix = rb.getString("DB_PREFIX");
       }
       catch (Exception e) {
         System.out.println("\n*** DBBean.java (setDBBean) --> 提示訊息不需理會 ， DB_Config.properties 請將下列變數值做更換，不換也無所謂啦!!" + "\nORA_DRIVER --> DB_DRIVER" + "\nORA_PREFIX --> DB_PREFIX");
         try {
           dbDriver = rb.getString("ORA_DRIVER");
           dbPrefix = rb.getString("ORA_PREFIX");
         }
         catch (Exception e1) {
           System.out.println("\n*** DBBean.java (setDBBean) --> 超級嚴重的錯誤 , 請檢查 DB_Config.properties 下列兩個設定值是否存在! ***" +
                              "\nORA_DRIVER" +
                              "\nORA_PREFIX");
         }
       }
       dbLocale = dbIDL;
       dbUser   = dbusr;
       dbPwd    = dbpwd;
     }
     init();
  }*/

  //當被加入Session物件時，會呼叫此方法
  /** This function will be called when bean be added in a session object.
    */
  public void valueBound(HttpSessionBindingEvent evt) {
   // System.out.println(evt.getName() + " Add To Session!");
  }

  //當被移出Session物件時，會呼叫此方法
  /** This function will be called when bean be removed from a session object.
    */
  public void valueUnbound(HttpSessionBindingEvent evt){
     //System.out.println(evt.getName() + " Remove From Session!");
     close();
  }

  /*private void init() {
    if ( extraBit == true && conn != null ) {
      //該DB_Bean接受外來的Connection
    } else {
      if ( dbDriver == null || dbPrefix == null || dbDriver.equals("") || dbPrefix.equals("") || (isSet)) {
        try {
          dbDriver = rb.getString("DB_DRIVER");
          dbPrefix = rb.getString("DB_PREFIX");
        }
        catch (Exception e) {
          System.out.println("\n*** DBBean.java (init) --> 提示訊息不需理會 ， DB_Config.properties 請將下列變數值做更換，不換也無所謂啦!!" + "\nORA_DRIVER --> DB_DRIVER" + "\nORA_PREFIX --> DB_PREFIX");
          try {
            dbDriver = rb.getString("ORA_DRIVER");
            dbPrefix = rb.getString("ORA_PREFIX");
          }
          catch (Exception e1) {
            System.out.println("\n*** DBBean.java (init) --> 超級嚴重的錯誤 , 請檢查 DB_Config.properties 下列兩個設定值是否存在! ***" +
                               "\nORA_DRIVER" +
                               "\nORA_PREFIX");
          }
        } //End-try
        dbLocale = rb.getString(ActSite + "_DBHost").toLowerCase().trim() + (rb.getString(ActSite + "_DBPort").trim().equals("") ? "" : ":" + rb.getString(ActSite + "_DBPort")) + (rb.getString(ActSite + "_DBSid").trim().equals("") ? "" : ":" + rb.getString(ActSite + "_DBSid"));
        dbUser   = (rb.getString(ActSite + "_DBUser").equals("") ? null : rb.getString(ActSite + "_DBUser"));
        dbPwd    = (rb.getString(ActSite + "_DBPwd").equals("") ? null : rb.getString(ActSite + "_DBPwd"));
      }
      dbPrefix = dbPrefix + dbLocale;
      try {
         這是透過ODBC 連接 ACCESS DB 的語法(注意!!stmt init 時多了兩個參數 第一個參數代表可以前後移動 第二個代表ResultSet可否直接update資料)
        String url = "jdbc:odbc:swdmdb";
        DriverManager.registerDriver((Driver) (Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance()));

        Connection conn = DriverManager.getConnection(url,"","");
        Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);

        DriverManager.registerDriver((Driver) (Class.forName(dbDriver).newInstance()));
        conn = DriverManager.getConnection(dbPrefix,dbUser,dbPwd);
      } catch (Exception e) {
        System.out.println("\n*** DBBean.java (init) --> 資料庫連線失敗 Database Connect Fail , 請檢查 DB_Config.properties 下列設定值是否正確! ***" +
                           "\nT_DBHost=" + rb.getString(ActSite + "_DBHost").toLowerCase() +
                           "\nT_DBPort=" + rb.getString(ActSite + "_DBPort") +
                           "\nT_DBSid=" + rb.getString(ActSite + "_DBSid") +
                           "\nT_DBUser=" + rb.getString(ActSite + "_DBUser") +
                           "\nT_DBPwd=" + rb.getString(ActSite + "_DBPwd") +
                           "\n*** End ***");
      }
    }
  }*/

  /** return Connection. If connection be created.
   * GM change public to private 2005/10/15 because connection generate from QueryBean
    
  private Connection getConnection() {
     return conn;
  }*/

  /** Drops all changes made since the previous commit/rollback and releases any database locks currently held by this Connection.
   *
    */
  public void rollback() throws java.sql.SQLException {
    if ( conn != null ) conn.rollback();
  }

  /** Save all changes made since the previous commit/rollback and releases any database locks currently held by this Connection.
   *
    */
  public void commit() throws java.sql.SQLException {
    if ( conn != null ) conn.commit();
  }

  /** Sets this connection's auto-commit mode.
   * GM change public to private 2005/10/15 because connection generate from QueryBean
    
  private void setAutoCommit(boolean auto) throws java.sql.SQLException {
     if ( conn != null ) conn.setAutoCommit(auto);
  }*/

  /**
   * Execute The Insert,Update SQL
   * @param  sqlStr. The SQL Statement.
   * @return boolean. True,if the sql statement execute success.
   */
  public boolean executeSQL(String sqlStr) {
     boolean retBit = false;

     try {

       preStmt  = conn.prepareStatement(sqlStr);
       preStmt.executeUpdate();
       retBit = true;
     } catch (SQLException se) {
       System.out.println("DBBean.java (executeSQL) --> UpdateSQL Exception:" + se.toString());
       System.out.println("DBBean.java (executeSQL) --> SQL:" + sqlStr);
     } finally {
       try {
         if ( preStmt != null ) { preStmt.close(); preStmt = null; }
       } catch (SQLException se) { System.out.println("DBBean.java (executeSQL) --> Close Statement Object Error:" + se.toString()); }
     }
     return retBit;
  }

  /**
   * Execute The Insert,Update SQL with parameter vector
   * @param  sqlStr. The SQL Statement.
   * @param  params. The Execute SQL Parameters
   * @return boolean. True,if the sql statement execute success.
   */
  public boolean executeSQL(String sqlStr, Vector<Object> params) {
     boolean retBit = false;

     try {
       preStmt  = conn.prepareStatement(sqlStr);

       if ( params != null ) {
         for ( int i=0; i < params.size(); i++ ) {
           Object paramObj = params.elementAt(i);
           if( paramObj == null )                        preStmt.setNull      (i+1, java.sql.Types.VARCHAR);
           else if ( paramObj instanceof String )        preStmt.setString    (i+1, (String)paramObj    );
           else if ( paramObj instanceof Timestamp )     preStmt.setTimestamp (i+1, (Timestamp)paramObj );
           else if ( paramObj instanceof java.sql.Date ) preStmt.setDate      (i+1, (java.sql.Date)paramObj );
           else if ( paramObj instanceof Integer )       preStmt.setInt       (i+1, ((Integer)paramObj).intValue() );
           else if ( paramObj instanceof Float )         preStmt.setFloat     (i+1, ((Float)paramObj).floatValue() );
           else if ( paramObj instanceof Double )        preStmt.setDouble    (i+1, ((Double)paramObj).doubleValue() );
           else if ( paramObj instanceof Long )          preStmt.setLong      (i+1, ((Long)paramObj).longValue() );
           else if ( paramObj instanceof byte[]    ) {
                     byte[] bdata = (byte[])paramObj;
                     ByteArrayInputStream bin = new ByteArrayInputStream(bdata);
                     preStmt.setBinaryStream(i+1 ,bin,bdata.length);
           }
         }
       }
       preStmt.executeUpdate();
       retBit = true;
     } catch (SQLException se) {
       System.out.println("DBBean.java (executeSQL) --> UpdateSQL Exception:" + se.toString());
       System.out.println("DBBean.java (executeSQL) --> SQL:" + sqlStr);
     } finally {
       try {
         if ( preStmt != null ) { preStmt.close(); preStmt = null; }
       } catch (SQLException se) { System.out.println("DBBean.java (executeSQL) --> Close Statement Object Error:" + se.toString()); }
     }
     return retBit;
  }

  /**
   * Execute The Insert,Update SQL with parameter vector
   * @param  sqlStr. The SQL Statement.
   * @param  params. The Execute SQL Parameters
   * @return boolean. True,if the sql statement execute success.
   */
  public boolean executeSQL(String sqlStr, Vector<Object>[] params) {
     boolean retBit = false;
     try {
       preStmt  = conn.prepareStatement(sqlStr);
       if ( params != null ) {
         for ( int i=0; i < params.length; i++ ) { //params.size() - 表共有幾筆
           Vector<Object> paramVec = (Vector<Object>)params[i];
           for ( int j=0; j < paramVec.size(); j++ ) {
             Object paramObj = paramVec.elementAt(j);
             if ( paramObj == null )                   preStmt.setNull( j+1, java.sql.Types.NULL);
             else if ( paramObj instanceof String )    preStmt.setString   (j+1, (String)paramObj    );
             else if ( paramObj instanceof Timestamp ) preStmt.setTimestamp(j+1, (Timestamp)paramObj );
             else if ( paramObj instanceof Integer )   preStmt.setInt      (j+1, ((Integer)paramObj).intValue()    );
             else if ( paramObj instanceof Double )    preStmt.setDouble   (i+1, ((Double)paramObj).doubleValue() );
             else if ( paramObj instanceof byte[]    ) {
                     byte[] bdata = (byte[])paramObj;
                     ByteArrayInputStream bin = new ByteArrayInputStream(bdata);
                     preStmt.setBinaryStream(j+1 ,bin,bdata.length);
             }
           }
           preStmt.addBatch();
         }
       }
       preStmt.executeBatch();
       retBit = true;
     } catch (SQLException se) {
       System.out.println("DBBean.java (executeSQL) --> UpdateSQLBatch Exception:" + se.toString());
       System.out.println("DBBean.java (executeSQL) --> SQL:" + sqlStr);
     } finally {
       try {
         if ( preStmt != null ) { preStmt.close(); preStmt = null; }
       } catch (SQLException se) { System.out.println("DBBean.java (executeSQL) --> Close Statement Object Error:" + se.toString()); }

     }
     return retBit;
  }
  /**
   * Execute The Query SQL with parameter vector
   * @param  sqlStr. The SQL Statement.
   * @param  params. The Execute SQL Parameters
   * @return ResultSet. The ResultSet for excute sql result.
   */
  public ResultSet querySQL(String sqlStr, Vector<Object> params) {
     try {
       //if ( rs != null ) { rs.close(); rs = null;  }
       //preStmt  = conn.prepareStatement(sqlStr,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
       //preStmt  = conn.prepareStatement(sqlStr,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
       if(dbType.toUpperCase().equals("ACCESS")){
           preStmt = conn.prepareStatement(sqlStr,ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY);
       }
       else{
           preStmt = conn.prepareStatement(sqlStr);
       }
       if ( params != null ) {
         for ( int i=0; i < params.size(); i++ ) {
           Object paramObj = params.elementAt(i);
           if ( paramObj instanceof String )	     preStmt.setString   (i+1, (String)paramObj    );
           else if ( paramObj instanceof Timestamp ) preStmt.setTimestamp(i+1, (Timestamp)paramObj );
           else if ( paramObj instanceof Integer )   preStmt.setInt      (i+1, ((Integer)paramObj).intValue() );
         }
         return preStmt.executeQuery();
       }
     } catch (SQLException se) {
       System.out.println("DBBean.java (querySQL) --> " + se.toString());
       System.out.println("DBBean.java (querySQL) --> SQL:" + sqlStr);
     }
     return null;
  }

  /**
   * Execute The Query SQL
   * @param  sqlStr. The SQL Statement.
   * @return ResultSet. The ResultSet for excute sql result.
   */
  public ResultSet querySQL(String sqlStr) {
     try {
       if ( rs != null ) { rs.close(); rs = null;  }
       if(dbType.toUpperCase().equals("ACCESS")){
           stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY);
       }
       else{
           stmt = conn.createStatement();
       }
       rs = stmt.executeQuery(sqlStr);
     } catch (SQLException se) {  System.out.println("DBBean.java (querySQL) --> " + se.toString());
     }
     return rs;
  }

  /**
   * Execute The Query SQL
   * @param  sqlStr. The SQL Statement.
   * @return boolean. false,if the sql statement execute no data found.
   */
  public boolean isDataFound(String sqlStr) {
     boolean status = false;
     try {
       rs = querySQL(sqlStr);
       if (rs.next()) status = true;
     } catch (SQLException se) {
       System.out.println("DBBean.java (isDataFound) --> " + se.toString());
       System.out.println("DBBean.java (querySQL) --> SQL:" + sqlStr);
     }
     return status;
  }

  /** Close the current connection, result set and prepare statement.
    */
  public void close() {
    try {
      if ( rs      != null ) { if(!dbType.toUpperCase().trim().equals("ACCESS")){rs.close();}      rs     = null;  }
      if ( preStmt != null ) { preStmt.close(); preStmt = null; }
      /**
       * GM remark 2005/10/15 because connection generate from QueryBean
       */
      /*
      if ( conn    != null ) {
        if ( !conn.isClosed() ) conn.close();
        conn = null;
      }*/
    } catch (SQLException se) { System.out.println("DBBean.java (close) --> DB Bean Connection Close Exception:" + se.toString()); }
  }
} //End-class
